﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;
using System.Windows.Forms;

namespace Trade.DAL
{
    public class SubjectMessage
    {
        //根据ParentId读取相应的子项数据
        public IList<SubjectInfo> GetSubjectByParentId(int parentId)
        {
            IList<SubjectInfo> subjectByParentId = new List<SubjectInfo>();

            string sql = "Select * from SubjectMessage where ParentId = @ParentId order by SubjectCode";

            SqlParameter parm = new SqlParameter("@ParentId", SqlDbType.Int);
            parm.Value = parentId;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, sql,parm))
            {
                while (rdr.Read())
                {
                    SubjectInfo subjectMessage = new SubjectInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetInt32(3), rdr.GetBoolean(4), rdr.GetBoolean(5), rdr.GetString(6));
                    subjectByParentId.Add(subjectMessage);
                }
            }

            return subjectByParentId;
        }

        //将数据写到数据库
        public void WriteToDatabase(string action, SubjectInfo subject)
        {
            string sql = "Exec Usp_ModifySubjectMessage @p_Action, @p_SubjectId, @p_SubjectCode, @p_SubjectName, @p_ParentId, @p_Disabled, @p_DetailFlag, @p_Description, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                new SqlParameter("@p_Action", SqlDbType.Char, 1),
                new SqlParameter("@p_SubjectId", SqlDbType.Int),
                new SqlParameter("@p_SubjectCode", SqlDbType.VarChar, 30), 
                new SqlParameter("@p_SubjectName", SqlDbType.VarChar, 100), 
                new SqlParameter("@p_ParentId", SqlDbType.Int), 
                new SqlParameter("@p_Disabled", SqlDbType.Bit), 
                new SqlParameter("@p_DetailFlag", SqlDbType.Bit), 
                new SqlParameter("@p_Description", SqlDbType.VarChar, 200),
                new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = subject.SubjectId;
            parms[2].Value = subject.SubjectCode.Trim();
            parms[3].Value = subject.SubjectName.Trim();
            parms[4].Value = subject.ParentId;
            parms[5].Value = subject.Disabled;
            parms[6].Value = subject.DetailFlag;
            parms[7].Value = subject.Description.Trim();
            parms[8].Value = "";
            parms[8].Direction = ParameterDirection.InputOutput;
            parms[9].Value = 0;
            parms[9].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
        }

        //生成新的科目编号
        public string GenNewSubjectCode(string subjectCode)
        {
            string newSubjectCode = "";
            string sql = "Select left(@SubjectCode, 1) + right(replicate(0,3) + cast(cast(max(substring(SubjectCode,2,3)) as int)+1 as varchar), 3) from SubjectMessage where left(SubjectCode, 1) = left(@SubjectCode, 1)";

            if (string.IsNullOrEmpty(subjectCode) == false)
            {
                SqlParameter parm = new SqlParameter("@SubjectCode", SqlDbType.VarChar, 30);

                parm.Value = subjectCode;

                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, sql, parm))
                {
                    while (rdr.Read())
                    {
                        newSubjectCode = rdr.GetString(0).Trim();
                    }
                }
            }

            return newSubjectCode;
        }
    }
}
